from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template

def query_trade(start_date, end_date, portf_list=None, tick_list=None):

	sql_tpl = Template('''select trd.trade_id,
								trd.trade_date,
								trd.ticker,
								trd.lticker,
								si."name",
								trd.security_type,
								trd.trade_type, 
								trd.amount,
								trd.cost_price,
								trd.currency,
								trd.settle_date,
								trd.portfolio_id,
								pi."name"
						 from transaction_tick_ex trd 
						 left join comm_security_static_info si
							on si.ticker=trd.lticker 
						 left join portfolio_info pi 
							on pi.portfolio_id=trd.portfolio_id 
						 join security_type_rank r 
							on r.sec_type=trd.security_type 
						 where trade_date>=TO_DATE('${START_DATE}','yyyy-mm-dd') 
							and trade_date<=TO_DATE('${END_DATE}','yyyy-mm-dd')  
							and ${PORTF_CRITERIA} 
							and ${TICK_CRITERIA}
						 order by trade_date,trd.portfolio_id,r.rank,trd.lticker''')
	sql_text = sql_tpl.substitute(START_DATE=start_date.isoformat(), 
								END_DATE=end_date.isoformat(), 
								TICK_CRITERIA=format_sql_criteria('trd.lticker', tick_list), 
								PORTF_CRITERIA=format_sql_criteria('trd.portfolio_id', portf_list))	
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
		
		
	
	
	
	